package birs.controller.staff.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.staff.STF004Model;
import birs.model.staff.STF004ModelA;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STF004Task {
	
	public void getUniversityEducatior(HttpServletRequest request, HttpServletResponse response) throws IOException{
		List<STF004Model> stf004ModelList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<STF004Model> stf004Model = new ResultSetMapper<STF004Model>();
			stf004ModelList = stf004Model.mapRersultSetToObject(getUniversityEducationSQL(con), STF004Model.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(stf004ModelList);
		sendResponse(request, response, resultJson);
	}
	
	public void getFirstDrilldown(HttpServletRequest request, HttpServletResponse response){
		String depId = request.getParameter("depId");
		String acadtypeCode = "(";
		String degreeLevelCode = "(";
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSet acadRs = getAcadpositiontypeSQL(con, depId);
			while(acadRs.next()){
				acadtypeCode += acadRs.getInt(1) + ",";
			}
			acadtypeCode = acadtypeCode.substring(0,acadtypeCode.lastIndexOf(",")) +")";
			
			ResultSet degreeRs = getDegreeLevelCodeSQL(con, depId,acadtypeCode);
			while(degreeRs.next()){
				degreeLevelCode += degreeRs.getInt(1) + ","; 
			}
			degreeLevelCode = degreeLevelCode.substring(0,degreeLevelCode.lastIndexOf(","))+")";
			
			ResultSetMapper<STF004ModelA> stf004ModelA = new ResultSetMapper<STF004ModelA>();
			List<STF004ModelA> stf004ModelAList = stf004ModelA.mapRersultSetToObject(getFirstDrilldown(con, depId, acadtypeCode, degreeLevelCode), STF004ModelA.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(stf004ModelAList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private ResultSet getUniversityEducationSQL(Connection con){
		String sql = " SELECT V.DEPARTMENTID,V.DEPARTMENTNAME, COUNT(V.STAFFID) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE V.DEPARTMENTID IN (62,70,77,84,91,97,124,335,354,393)	"
				+" 	AND V.STAFFGROUP = 1	"
				+" 	GROUP BY  V.DEPARTMENTID,V.DEPARTMENTNAME	"
				+" 	ORDER BY V.DEPARTMENTID ";
		return getData(sql, con);
	}
	
	private ResultSet getAcadpositiontypeSQL(Connection con,String depId){
		String sql = " SELECT DISTINCT V.ACADPOSITIONTYPE	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE V.DEPARTMENTID = "+depId
				+" 	AND V.STAFFGROUP = 1";
		return getData(sql, con);
	}
	
	private ResultSet getDegreeLevelCodeSQL(Connection con,String depId,String acadtypeCode){
		String sql = " 	SELECT DISTINCT V.DEGREELEVEL	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE V.DEPARTMENTID = "+depId
				+" 	AND V.STAFFGROUP = 1	"
				+" 	AND V.ACADPOSITIONTYPE in "+acadtypeCode;

		return getData(sql,con);
	}
	
	private ResultSet getFirstDrilldown(Connection con,String depId,String acadtypeCode,String degreeLevelCode){
		String sql = " 	SELECT V.DEPARTMENTID,V.DEPARTMENTNAME,V.ACADPOSITIONTYPE,	"
				+" 	(SELECT S.BYTEDES 	"
				+" 	FROM SYSBYTEDES S	"
				+" 	WHERE S.TABLENAME = 'ACADPOSITION'	"
				+" 	AND S.COLUMNNAME = 'ACADPOSITIONTYPE'	"
				+" 	AND S.BYTECODE = V.ACADPOSITIONTYPE	"
				+" 	) ACADPOSITIONNAME,	"
				+" 	V.DEGREELEVEL,	"
				+" 	(SELECT S.BYTEDES 	"
				+" 	FROM SYSBYTEDES S	"
				+" 	WHERE S.TABLENAME = 'DEGREE'	"
				+" 	AND S.COLUMNNAME = 'DEGREELEVEL'	"
				+" 	AND S.BYTECODE = V.DEGREELEVEL	"
				+" 	) DEGREELEVELNAME,	"
				+" 	'ปกติ' AS STATUS,	"
				+" 	COUNT(V.ACADPOSITIONID) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V, STAFF S,SYSBYTEDES SYS	"
				+" 	WHERE V.DEPARTMENTID = "+depId
				+" 	AND V.STAFFGROUP = 1	"
				+" 	AND V.ACADPOSITIONTYPE in "+acadtypeCode
				+" 	AND V.DEGREELEVEL in "+degreeLevelCode
				+" 	AND V.STAFFID = S.STAFFID	"
				+" 	AND S.STAFFSTATUS = SYS.BYTECODE	"
				+" 	AND S.STAFFSTATUS <> 21	"
				+" 	AND SYS.TABLENAME = 'STAFFSTATUS'	"
				+" 	AND SYS.COLUMNNAME = 'STAFFSTATUS'	"
				+" 	GROUP BY V.DEPARTMENTID,V.DEPARTMENTNAME,V.ACADPOSITIONTYPE,V.DEGREELEVEL	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT V.DEPARTMENTID,V.DEPARTMENTNAME,V.ACADPOSITIONTYPE,	"
				+" 	(SELECT S.BYTEDES 	"
				+" 	FROM SYSBYTEDES S	"
				+" 	WHERE S.TABLENAME = 'ACADPOSITION'	"
				+" 	AND S.COLUMNNAME = 'ACADPOSITIONTYPE'	"
				+" 	AND S.BYTECODE = V.ACADPOSITIONTYPE	"
				+" 	) ACADPOSITIONNAME,	"
				+" 	V.DEGREELEVEL,	"
				+" 	(SELECT S.BYTEDES 	"
				+" 	FROM SYSBYTEDES S	"
				+" 	WHERE S.TABLENAME = 'DEGREE'	"
				+" 	AND S.COLUMNNAME = 'DEGREELEVEL'	"
				+" 	AND S.BYTECODE = V.DEGREELEVEL	"
				+" 	) DEGREELEVELNAME,	"
				+" 	'ลาศึกษาต่อ' AS STATUS,COUNT(V.ACADPOSITIONID) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V, STAFF S,SYSBYTEDES SYS	"
				+" 	WHERE V.DEPARTMENTID = "+depId
				+" 	AND V.STAFFGROUP = 1	"
				+" 	AND V.ACADPOSITIONTYPE in "+acadtypeCode
				+" 	AND V.DEGREELEVEL in "+degreeLevelCode
				+" 	AND V.STAFFID = S.STAFFID	"
				+" 	AND S.STAFFSTATUS = SYS.BYTECODE	"
				+" 	AND S.STAFFSTATUS = 21	"
				+" 	AND SYS.TABLENAME = 'STAFFSTATUS'	"
				+" 	AND SYS.COLUMNNAME = 'STAFFSTATUS'	"
				+" 	GROUP BY V.DEPARTMENTID,V.DEPARTMENTNAME,V.ACADPOSITIONTYPE,V.DEGREELEVEL	"
				+" 	ORDER BY ACADPOSITIONTYPE,DEGREELEVEL";
		return getData(sql, con);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}